use rusqlite::{Connection,Result};
use crate::data::db_path;
use crate::domin::{Order, OrderVO};


#[tauri::command]
pub fn order_del_by_id(id: String) -> usize{
    let sql = "delete from tb_order where id = ? ;";
    let conn = Connection::open(db_path()).unwrap();
    let mut stat = conn.prepare(sql).unwrap();
    stat.execute([id]).unwrap()
}

/// # 根据时间查询订单
#[tauri::command]
pub fn search_order_by_goods (start_time:String, end_time:String) -> String {
    let sql = "select goods_id,  sell_count, tt.money as money , tg.count, tg.price , tg.transport from tb_order tt inner join tb_goods tg on tt.goods_id = tg.id and tt.time > ? and tt.time < ? ;";
    let conn = Connection::open(db_path()).unwrap();
    let mut stat = conn.prepare(sql).unwrap();
    let rows = stat.query_map([start_time, end_time], |row| {
        Ok(OrderVO {
            goods_id: row.get(0).unwrap(),
            sell_count: row.get(1).unwrap(),
            money: row.get(2).unwrap(),
            count: row.get(3).unwrap(),
            price: row.get(4).unwrap(),
            transport: row.get(5).unwrap(),
        })
    }).unwrap();

    let mut vec = Vec::new() ;
    rows.for_each(|row|{
        vec.push(row.unwrap())
    });
    serde_json::to_string(&vec).unwrap()
}


/// # 根据商品id查询订单
pub fn order_find_by_goods_id(goods_id:String) -> Result<String>{
    let sql = "select * from tb_order where goods_id = ?;";
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map([goods_id], |row| {
        Ok(Order {
            id: row.get(0)?,
            goods_id: row.get(1)?,
            goods_name: row.get(2)?,
            sell_count: row.get(3)?,
            status: row.get(4)?,
            money: row.get(5)?,
            time: row.get(6)?,
            phone: row.get(7)?,
            address: row.get(8)?,
        })
    })?;
    let mut vec = Vec::new() ;
    rows.for_each(|row|{
        vec.push(row.unwrap())
    });
    Ok(serde_json::to_string(&vec).unwrap())
}

/// # 修改订单
/*pub fn order_update(order:Order) -> Result<usize>{
    let mut i = 0 ;
    let sql = "update tb_order set goods_id = ? , count = ? , time = ? where id = ? ;";
    if order.id != "" {
        let conn = Connection::open(db_path())?;
        let mut stat = conn.prepare(sql)?;
        i = stat.execute([order.goods_id,order.count.to_string(),order.time,order.id])?;
    }
    Ok(i)
}*/

/// # 根据时间查询订单
pub fn order_search_by_condiction(start_time: String, end_time: String) -> Result<String>{
    let sql = "select * from tb_order where time > ? and time < ?;";
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map([start_time, end_time], |row| {
        Ok(Order {
            id: row.get(0)?,
            goods_id: row.get(1)?,
            goods_name: row.get(2)?,
            sell_count: row.get(3)?,
            status: row.get(4)?,
            money: row.get(5)?,
            time: row.get(6)?,
            phone: row.get(7)?,
            address: row.get(8)?,
        })
    })?;
    let mut vec = Vec::new() ;
    rows.for_each(|row|{
        vec.push(row.unwrap())
    });
    let s = serde_json::to_string(&vec).expect("转换订单失败");
    Ok(s)
}

pub fn order_serach_all () -> Result<String>{
    let sql = "select * from tb_order ;" ;
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map([], |row| {
        Ok(Order {
            id: row.get(0)?,
            goods_id: row.get(1)?,
            goods_name: row.get(2)?,
            sell_count: row.get(3)?,
            status: row.get(4)?,
            money: row.get(5)?,
            time: row.get(6)?,
            phone: row.get(7)?,
            address: row.get(8)?,
        })
    })?;
    let mut vec = Vec::new();
    for g in rows {
        vec.push(g.unwrap())
    }
    let s = serde_json::to_string(&vec).expect("序列化数据出错");
    Ok(s)
}

#[tauri::command]
/// # 订单表添加数据
pub fn order_insert (order:Order) -> usize{
    let mut conn = Connection::open(db_path()).expect("打开数据库失败");
    let tr = conn.transaction().expect("开启事务失败");
    let e1 = tr.execute("insert into tb_order values (?,?,?,?,?,?,?,?,?);",[&order.id.to_string(),&order.goods_id,&order.goods_name,&order.sell_count.to_string(),&order.status.to_string(),&order.money.to_string(),&order.time.to_string(),&order.phone.to_string(),&order.address.to_string()]).expect("添加order数据失败");
    let e2 = tr.execute("UPDATE tb_repo SET count = (select count from tb_repo where id = ? ) + ? WHERE id = ?",[&order.goods_id,&order.sell_count.to_string(),&order.goods_id]).expect("添加repo数据失败");
    tr.commit().expect("提交事务失败");
    let mut i = 0 ;
    if e1 != 0 && e2 != 0 {
        i = 1 ;
    }
    i
}